import requests
import json
from datetime import datetime
import mysql
import mysql.connector
from amap_getter import AMAPDataCollector
import time


def from_percentage(percentage: str)->float:
    return float(percentage.rstrip('%'))/100


class DatabaseHandler:

    def __init__(self,# MySQL配置
        MYSQL_HOST = "localhost",
        MYSQL_USER = "root",
        MYSQL_PASSWORD = "030505Zyp",
        MYSQL_DATABASE = "traffic_db",
        location=None#location to get traffic data
        ):
        self.MYSQL_HOST = MYSQL_HOST
        self.MYSQL_USER = MYSQL_USER
        self.MYSQL_PASSWORD = MYSQL_PASSWORD
        self.MYSQL_DATABASE = MYSQL_DATABASE
        self.connection = mysql.connector.connect(
            host = self.MYSQL_HOST,
            user = self.MYSQL_USER,
            password = self.MYSQL_PASSWORD,
            database = self.MYSQL_DATABASE,
        )
        self.dataCollector=AMAPDataCollector(key="12387795d1551d9e4072904991bebab6",location=location)



    def fetch_traffic_info(self) -> json:
        """从API获取交通信息"""
        response = self.dataCollector.get_data()
        print("data fetched from API...")
        print(response.json())
        if response.status_code == 200:
            return response.json()
        else:
            raise Exception(f"Failed to fetch data from API: {response.status_code}")

    def save_to_database(self,data: json) -> None:
        try:
            cursor = self.connection.cursor()

            values = (
                data['trafficinfo']['description'],
                from_percentage(data['trafficinfo']['evaluation']['expedite']),
                from_percentage(data['trafficinfo']['evaluation']['congested']),
                from_percentage(data['trafficinfo']['evaluation']['blocked']),
                from_percentage(data['trafficinfo']['evaluation']['unknown']),
                int(data['trafficinfo']['evaluation']['status']),
                data['trafficinfo']['evaluation']['description']
            )

            insert_query = """
            INSERT INTO traffic_data (
                traffic_condition,
                evaluation_expedite, evaluation_congested, evaluation_blocked,
                evaluation_unknown, evaluation_status, evaluation_description
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            """

            cursor.execute(insert_query, values)
            self.connection.commit()

            print("Data saved successfully.")

        except mysql.connector.Error as err:
            print(f"Error: {err}")

        finally:
            cursor.close()



    def query_traffic_data(self,start_time, end_time):
        """查询指定时间范围内的交通数据"""
        cursor = self.connection.cursor()
        query = """
        SELECT * FROM traffic_data 
        """
        cursor.execute(query)
        results = cursor.fetchall()

        cursor.close()
        self.connection.close()
        return results


    def show(self):
        try:
            print("Data processed successfully.")
            query_results = self.query_traffic_data("2024-06-01 00:00:00", "2025-06-02 23:59:59")
            print(query_results)
        except Exception as e:
            print(f"Error processing data: {e}")

    def get(self):
        try:
            traffic_data = self.fetch_traffic_info()
            self.save_to_database(traffic_data)
            print("Data processed successfully.")
            time.sleep(2)  # 等待60秒后再次获取数据
        except KeyboardInterrupt:
            print("Program stopped by user.")
        except Exception as e:
            print(f"Error processing data: {e}")

def main():
    handler = DatabaseHandler(
        MYSQL_PASSWORD="A7btngqx"
    )
    print("1 获取数据 存入数据库")
    print("2 输出数据库全部数据")
    choice=input("请选择：")
    if choice== "1":
        handler.get()
    elif choice== "2":
        handler.show()
    else:
        print("无效选项")

if __name__ == "__main__":
    main()
